/* 
 * (C) 2013 Daniel Garcia <contacto {at} danigarcia.org>
 * 
 * http://danigarcia.org
 * 
 * This file is part of BlackList.
 * 
 * BlackList is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * BlackList is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with BlackList.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */
package org.danigarcia.android.blacklist.database;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Acts as intermediate between application and database.
 * The code inside this class was originally a test, so although it works, it is
 * a mess and MUST be recoded and revised in following revisions.
 * @author Dani
 *
 */
public class DataManager extends SQLiteOpenHelper {

	public static final int SUCCESS = 0;
	public static final int ERROR = -1;
	public static final int ERROR_ID_EXISTS = -2;
	public static final int ERROR_ID_DOES_NOT_EXIST = -3;
	public static final String DB_NAME = "blacklist.db";
	public static final int	DB_VERSION = 1;
	
	public static final String COLUMN_BLACKLIST_PHONE = "phoneNumber";
	public static final String COLUMN_BLACKLIST_NAME = "contactName";
	public static final String COLUMN_BLACKLIST_CREATIONDATE = "creationDate";
	public static final String COLUMN_BLACKLIST_BLOCKED = "isBlocked";
	public static final String COLUMN_BLACKLIST_ID = "_id";
	
	public static final String COLUMN_CALLS_PHONE = "phoneNumber";
	public static final String COLUMN_CALLS_ID = "_id";
	public static final String COLUMN_CALLS_CREATIONDATE = "callDate";

	public static final String COLUMN_SMS_ID = "_id";
	public static final String COLUMN_SMS_SENDER = "sender";
	public static final String COLUMN_SMS_TARGET = "target";
	public static final String COLUMN_SMS_BODY = "body";
	public static final String COLUMN_SMS_CREATIONDATE = "creationDate";
	
	private final String TAG = this.getClass().getName();
	
	private final String SQL_TABLE_BLACKLIST = "blacklist";
	private final String SQL_TABLE_CALLS = "calls";
	private final String SQL_TABLE_SMS = "sms";
	
	private final String SQL_CREATE_CALLS = "create table "+ SQL_TABLE_CALLS +"(" +
												COLUMN_CALLS_ID + " integer primary key autoincrement," +
												COLUMN_CALLS_PHONE + " text," +
												COLUMN_CALLS_CREATIONDATE + " date)";
	
	private final String SQL_CREATE_BLACKLIST = "create table "+ SQL_TABLE_BLACKLIST + "(" +
														COLUMN_BLACKLIST_ID + " integer primary key autoincrement," +
														COLUMN_BLACKLIST_PHONE + " text," +
														COLUMN_BLACKLIST_NAME + " text," +
														COLUMN_BLACKLIST_CREATIONDATE + " date," +
														COLUMN_BLACKLIST_BLOCKED + " integer not null default 1)";
	
	private final String SQL_CREATE_SMS = "create table " + SQL_TABLE_SMS + "(" +
														COLUMN_SMS_ID + " integer primary key autoincrement," +
														COLUMN_SMS_SENDER + " text," +
														COLUMN_SMS_TARGET + " text," +
														COLUMN_SMS_BODY + " text," +
														COLUMN_SMS_CREATIONDATE + " date)";
	
	private final String SQL_DROP_CALLS = "drop table if exists " + SQL_TABLE_CALLS;
	private final String SQL_DROP_BLACKLIST = "drop table if exists " + SQL_TABLE_BLACKLIST;
	private final String SQL_DROP_SMS = "drop table if exists " + SQL_TABLE_SMS;
		
	public DataManager(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_BLACKLIST);
		db.execSQL(SQL_CREATE_CALLS);
		db.execSQL(SQL_CREATE_SMS);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(SQL_DROP_SMS);
		db.execSQL(SQL_DROP_CALLS);
		db.execSQL(SQL_DROP_BLACKLIST);
		onCreate(db);
	}

	/**
	 * Checks if a phoneNumber is present in the database
	 * @param phoneNumber Number to check
	 * @return true if it's present, false otherwise
	 */
	public boolean exists(String phoneNumber)
	{
		String[] selectFilter = {phoneNumber};
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_BLACKLIST, null, COLUMN_BLACKLIST_PHONE +"=?", selectFilter, null, null, null);
		cSelect.moveToFirst();
		boolean result = cSelect.getCount() > 0; 
		
		Log.d(TAG, String.format("exists(%s): %s", phoneNumber, cSelect.getCount()));
		
		if(cSelect.isClosed())
			cSelect.close();
		
		return result;
	}
	
	/**
	 * Checks if a phoneNumber ID is present in the database
	 * @param phoneId ID to check
	 * @return true if it's present, false otherwise
	 */
	public boolean exists(int phoneId)
	{
		String[] selectFilter = {String.valueOf(phoneId)};
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_BLACKLIST, null, COLUMN_BLACKLIST_ID +"=?", selectFilter, null, null, null);
		cSelect.moveToFirst();
		boolean result = cSelect.getCount() > 0; 
		
		Log.d(TAG, String.format("exists(%s): %s", phoneId, cSelect.getCount()));
		
		if(cSelect.isClosed())
			cSelect.close();
		
		return result;
	}
	
	/**
	 * Checks if a phoneNumber is present in the database. Because sometimes phone numbers
	 * can store preffixes like international codes, this method provides a way to ignore
	 * the begining of the phoneNumber to check if it's already stored.
	 * In example, if we have stored the phone 555000111 and we want to check if the phone
	 * +44555000111 is stored in our database, it will return true if the parameter 
	 * considerEndOfNumber is also true
	 * @param phoneNumber Number to check
	 * @param considerEndOfNumber True if we want filter with a like %phoneNumber filter
	 * @return true if it's present, false otherwise
	 */
	public boolean exists(String phoneNumber, boolean considerEndOfNumber)
	{
		phoneNumber = (considerEndOfNumber ? '%' + phoneNumber  : phoneNumber);
		String comparation = (considerEndOfNumber ? " like ?" : " = ?");
		
		String[] selectFilter = {phoneNumber};
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_BLACKLIST, null, COLUMN_BLACKLIST_PHONE + comparation, selectFilter, null, null, null);
		cSelect.moveToFirst();
		boolean result = cSelect.getCount() > 0; 
		
		Log.d(TAG, String.format("exists(%s): %s", phoneNumber, cSelect.getCount()));
		
		if(cSelect.isClosed())
			cSelect.close();
		
		return result;
	}
	
	/**
	 * Get the number of blocked calls. Can be filtered by phone Number
	 * @param phoneNumber Search filter. If null, it will retrieve the total amount of blocked calls
	 * @return Number of blocked calls
	 */
	public int getTotalCalls(String phoneNumber)
	{
		Log.d(TAG, "getTotalCalls()");
		String[] selectFilter = {phoneNumber};
		SQLiteDatabase db = getWritableDatabase();
		
		Cursor cSelect = null;
		if(phoneNumber != null)
			cSelect = db.query(SQL_TABLE_CALLS, null, COLUMN_BLACKLIST_PHONE +"=?", selectFilter, null, null, null);
		else
			cSelect = db.query(SQL_TABLE_CALLS, null, null, null, null, null, null);
		
		int result = cSelect.getCount();
		
		if(cSelect.isClosed())
			cSelect.close();
		
		return result < 0 ? 0 : result;
	}
	
	/**
	 * Retrieves the number of phones stored in the block list
	 * @return Number of blocked phones
	 */
	public int getTotalNumbers()
	{
		Log.d(TAG, "getTotalNumbers()");
		Cursor c = getNumbers(null);
		int result = c.getCount();
		if(!c.isClosed())
			c.close();
		
		return result;
	}
	
	/**
	 * Get the number of blocked SMS. It can be filtered by phone Number
	 * @param phoneNumber Search filter. If null, it will retrieve the total amount of blocked sms
	 * @return Number of blocked sms
	 */
	public int getTotalSms(String phoneNumber)
	{
		Log.d(TAG, "getTotalSms()");
		Cursor c = getSms(phoneNumber, null);
		int result = c.getCount();
		if(!c.isClosed())
			c.close();
		
		return result;
	}
	
	/**
	 * Retrieves a cursor with the phone numbers.
	 * @param phoneNumber Filter phoneNumber. It will return all if null.
	 * @param orderBy Order by clause
	 * @return Cursor with the matching elements
	 */
	public Cursor getNumbers(String phoneNumber, String orderBy)
	{
		Log.d(TAG, String.format("getNumbers(%s)", phoneNumber == null ? "null" : phoneNumber));
		if(phoneNumber != null)
			phoneNumber = '%' + phoneNumber + '%';

		Log.d(TAG, String.format("getNumbers(%s)", phoneNumber == null ? "null" : phoneNumber));
		
		String[] selectFilter = {phoneNumber};
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_BLACKLIST, null, (phoneNumber != null ? COLUMN_BLACKLIST_PHONE + " like ?" : null), (phoneNumber != null ? selectFilter : null), null, null, orderBy);
		
		return cSelect;
	}
	
	/**
	 * Retrieves a cursor with the phone numbers with the default order.
	 * @param phoneNumber Filter phoneNumber. It will return all if null.
	 * @return Cursor with the matching elements
	 */
	public Cursor getNumbers(String phoneNumber)
	{
		return getNumbers(phoneNumber, null);
	}
	
	/**
	 * Retrieves a string array with all the blocked phone numbers.
	 * @return Array with all the phones of the database
	 */
	public String[] getAllBlockedNumbersAsArray()
	{
		Log.d(TAG, "getAllBlockedNumbersArray()");
		Cursor c = getNumbers(null);
		ArrayList<String> numberArrayList = new ArrayList<String>();
		if((c != null) && (c.getCount() > 0))
		{
			for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
			{
				numberArrayList.add(c.getString(c.getColumnIndex(COLUMN_BLACKLIST_PHONE)));
			}
		}
		
		if(!c.isClosed())
			c.close();
		
		String[] result = null;
		if(!numberArrayList.isEmpty())
		{
			result = new String[numberArrayList.size()];
			for(int i=0;i<numberArrayList.size();i++)
				result[i] = numberArrayList.get(i);
		}
		
		return result;
	}
	
	/**
	 * Provides the phone number corresponding to an ID
	 * @param id ID to check
	 * @return String with the desired phone number
	 */
	public String getNumberById(int id)
	{
		String result = null;
		
		Log.d(TAG, String.format("getNumber(%s)", id));
		if(id <= 0)
			return null;
		
		String[] selectFilter = {String.valueOf(id)};
		String[] columns = {COLUMN_BLACKLIST_PHONE};
		
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_BLACKLIST, columns, COLUMN_BLACKLIST_ID + "=?", selectFilter, null, null, null);
		
		if(cSelect.moveToFirst())
			result = cSelect.getString(0);
		
		if(!cSelect.isClosed())
			cSelect.close();
		
		return result;
	}
	
	/**
	 * Gets the blocked calls stored in the database.
	 * @param phoneNumber Filter to retrieve just some phones. It will retrieve all if null.
	 * @param orderBy OrderBy clause
	 * @return Cursor with the matching registers.
	 */
	public Cursor getCalls(String phoneNumber, String orderBy)
	{
		Log.d(TAG, String.format("getCalls(%s)", phoneNumber == null ? "null" : phoneNumber));
		if(phoneNumber != null)
			phoneNumber = '%' + phoneNumber + '%';
		
		String[] selectFilter = {phoneNumber};
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_CALLS, null, (phoneNumber != null? COLUMN_CALLS_PHONE +" like ?" : null), (phoneNumber != null ? selectFilter : null), null, null, orderBy);
		
		return cSelect;
	}
	
	/**
	 * Gets the blocked calls stored in the database with default order.
	 * @param phoneNumber Filter to retrieve just some phones. It will retrieve all if null.
	 * @return Cursor with the matching registers.
	 */
	public Cursor getCalls(String phoneNumber)
	{
		return getCalls(phoneNumber, null);
	}
	
	/**
	 * Gets the blocked sms stored in the database.
	 * @param phoneNumber Filter to retrieve just some sms. It will retrieve all if null.
	 * @param orderBy OrderBy clause
	 * @return Cursor with the matching registers.
	 */
	public Cursor getSms(String phoneNumber, String orderBy)
	{
		Log.d(TAG, String.format("getSms(%s)", phoneNumber == null ? "null" : phoneNumber));
		
		String[] selectFilter = {phoneNumber};
		SQLiteDatabase db = getWritableDatabase();
		Cursor cSelect = db.query(SQL_TABLE_SMS, null, (phoneNumber != null? COLUMN_SMS_TARGET +" = ?" : null), (phoneNumber != null ? selectFilter : null), null, null, orderBy);
		
		return cSelect;
	}
	
	/**
	 * Inserts a number in the database
	 * @param phoneNumber Phone number to insert
	 * @param contactName Name of the contact
	 * @return Result of the operation
	 */
	public int insertNumber(String phoneNumber, String contactName)
	{
		Log.d(TAG, String.format("insertNumber(%s, %s)", phoneNumber, contactName));
		if(exists(phoneNumber))
			return ERROR_ID_EXISTS;
		
		ContentValues row = new ContentValues();
		row.put(COLUMN_BLACKLIST_PHONE, phoneNumber);
		row.put(COLUMN_BLACKLIST_NAME, contactName);
		row.put(COLUMN_BLACKLIST_CREATIONDATE, new Date().getTime());
		row.put(COLUMN_BLACKLIST_BLOCKED, 1);
		
		Log.d(TAG, String.format("insertNumber(%s, %s): Writing...", phoneNumber, contactName));
		SQLiteDatabase db = getWritableDatabase();
		long result = db.insert(SQL_TABLE_BLACKLIST, null, row);
		
		Log.d(TAG, String.format("insertNumber(%s, %s): Result: %s", phoneNumber, contactName, result));
		return (result == -1 ? ERROR : SUCCESS);
	}
	
	/**
	 * Inserts a blocked call in the database
	 * @param phoneNumber Phone number of the caller
	 * @return Result of the operation
	 */
	public int insertCall(String phoneNumber)
	{
		
		ContentValues row = new ContentValues();
		row.put(COLUMN_CALLS_PHONE, phoneNumber);
		row.put(COLUMN_CALLS_CREATIONDATE, new Date().getTime());
		
		SQLiteDatabase db = getWritableDatabase();
		long result = db.insert(SQL_TABLE_CALLS, null, row);
		Log.d(TAG, String.format("insertCall(%s): %s", phoneNumber, result));
		
		return (result == -1 ? ERROR : (int)result);
	}
	
	/**
	 * Inserts a blocked sms in the database
	 * @param sender Phone number of the sender. Can be from ISP or the caller itself
	 * @param target Phone number of the target, corresponding to the caller
	 * @param sms sms body
	 * @return Result of the operation
	 */
	public int insertSms(String sender, String target, String sms)
	{
		Log.d(TAG, String.format("insertSms(%s, %s, %s)", sender, target, sms));
		
		ContentValues row = new ContentValues();
		row.put(COLUMN_SMS_SENDER, sender);
		row.put(COLUMN_SMS_TARGET, target);
		row.put(COLUMN_SMS_CREATIONDATE, new Date().getTime());
		row.put(COLUMN_SMS_BODY, sms);
		
		SQLiteDatabase db = getWritableDatabase();
		long result = db.insert(SQL_TABLE_SMS, null, row);
		
		return (result == -1 ? ERROR : SUCCESS);
	}
	
	/**
	 * Updates a blocked phone number using phone number as key. Coded by a wrong
	 * initial approach to data model. Use the other method instead.
	 * @param phoneNumber Phone number to update
	 * @param name Name of the contact
	 * @param block Will be always true
	 * @return Result of the operation
	 */
	@Deprecated
	public int updateNumber(String phoneNumber, String name, boolean block)
	{
		if(!exists(phoneNumber))
			return ERROR_ID_DOES_NOT_EXIST;
		
		String[] whereFilter = {phoneNumber};
		
		ContentValues row = new ContentValues();
		row.put(COLUMN_BLACKLIST_NAME, name);
		row.put(COLUMN_BLACKLIST_BLOCKED, block ? 1 : 0);
		
		SQLiteDatabase db = getWritableDatabase();
		long result = db.update(SQL_TABLE_BLACKLIST, row, COLUMN_BLACKLIST_PHONE +"=?", whereFilter);
		
		return (result == -1 ? ERROR : (int)result);
	}
	
	/**
	 * Updates a blocked phone number.
	 * @param id ID of the register
	 * @param phoneNumber Phone number to update
	 * @param name  Name of the contact
	 * @return Result of the operation
	 */
	public int updateNumber(int id, String phoneNumber, String name)
	{
		String[] whereFilter = {String.valueOf(id)};
		
		ContentValues row = new ContentValues();
		row.put(COLUMN_BLACKLIST_NAME, name);
		row.put(COLUMN_BLACKLIST_PHONE, phoneNumber);
		row.put(COLUMN_BLACKLIST_BLOCKED, 1);
		
		SQLiteDatabase db = getWritableDatabase();
		long result = db.update(SQL_TABLE_BLACKLIST, row, COLUMN_BLACKLIST_ID +"=?", whereFilter);
		
		return (result == -1 ? ERROR : (int)result);
	}
	
	/**
	 * Deletes an individual SMS giving its ID
	 * @param id ID of the SMS to delete
	 * @return Number of registers deleted
	 */
	public int deleteSms(int id)
	{
		int result = 0;
		
		String[] whereFilter = {String.valueOf(id)};
		SQLiteDatabase db = getWritableDatabase();
		
		if(id <= 0)
			db.delete(SQL_TABLE_SMS,null, null);
		else
			result = db.delete(SQL_TABLE_SMS,  "_id=?", whereFilter);
		
		return result;
	}
	
	/**
	 * Deletes an individual call giving its ID
	 * @param id ID of the call to delete
	 * @return Number of registers deleted
	 */
	public int deleteCall(int id)
	{
		int result = 0;
		
		String[] whereFilter = {String.valueOf(id)};
		SQLiteDatabase db = getWritableDatabase();
		
		if(id <= 0)
			db.delete(SQL_TABLE_CALLS,null, null);
		else
			result = db.delete(SQL_TABLE_CALLS,  "_id=?", whereFilter);
		
		return result;
	}
	
	/**
	 * Deletes an individual blocked number
	 * @param id phoneNumber to delete
	 * @return Number of registers deleted
	 */
	public int deleteNumber(String phoneNumber)
	{
		int result = 0;
		SQLiteDatabase db = getWritableDatabase();
		
		if(phoneNumber == null)
		{
			db.beginTransaction();
			try {
				//db.delete(SQL_TABLE_CALLS, null, null);
				//db.delete(SQL_TABLE_SMS, null, null);
				db.delete(SQL_TABLE_BLACKLIST, null, null);
				db.setTransactionSuccessful();
			}
			catch(Exception e) {
				Log.e(TAG, "deleteNumber(): Error on transaction", e);
				return ERROR;
			}
			finally {
				db.endTransaction();
			}
		}
		
		else if(!exists(phoneNumber))
			return ERROR_ID_DOES_NOT_EXIST;
		
		String[] whereFilter = {phoneNumber};

		db.beginTransaction();
		try {
			//db.delete(SQL_TABLE_CALLS, COLUMN_CALLS_PHONE +"=?", whereFilter);
			db.delete(SQL_TABLE_BLACKLIST, COLUMN_BLACKLIST_PHONE +"=?", whereFilter);
			db.setTransactionSuccessful();
		}
		catch(Exception e) {
			Log.e(TAG, "deleteNumber(): Error on transaction", e);
			return ERROR;
		}
		finally {
			db.endTransaction();
		}
		
		return result;
		
	}
	
}
